require 'dbaccess.rb'
require 'rubygems'
require 'yahoofinance'
require 'ruby-debug'
require 'time'


class StandardQuote < DbAccess
  
  def initialize(hostname, username, password, dbname)
    super(hostname, username, password, dbname)
    @mycon = connect()
    @mycon.select_db(@dbname)
  end
  
  def close
    @mycon.close
  end
  
  def validrecord(qt)
  
    valid = true
    if (qt.date.to_s.match("N/A") || qt.time.to_s.match("N/A")) then
      valid = false
    end
    
    return(valid)
     
  end
  
  
  
  def loadstandardquote(symbols)
  
    quote_type = YahooFinance::StandardQuote
    
    # Get the quotes from Yahoo! Finance.  The get_quotes method call
    # returns a Hash containing one quote object of type "quote_type" for
    # each symbol in "quote_symbols".  If a block is given, it will be
    # called with the quote object (as in the example below).
    YahooFinance::get_quotes( quote_type, symbols) do |qt|
      
        if (validrecord(qt) == true) then
          puts "QUOTING: #{qt.symbol}"
          puts qt.to_s
          
          sql = "REPLACE INTO `standardquote` (`ask`, `bid`, `volume`, `dayLow`, `dayHigh`, `open`, `previousClose`, 
            `changePercent`, `name`, `symbol`, `changePoints`, `lastTrade`, `tradeTime`, `tradeDate`, `averageDailyVolume`) 
            VALUES(#{qt.ask.to_f}, #{qt.bid.to_f}, #{qt.volume.to_f}, #{qt.dayLow.to_f}, #{qt.dayHigh.to_f}, #{qt.open.to_f}, 
            #{qt.previousClose.to_f}, #{qt.changePercent.to_f}, '#{qt.name.delete("'").to_s}', '#{qt.symbol.to_s}', #{qt.changePoints.to_f}, 
            #{qt.lastTrade.to_f}, '#{Time.parse(qt.time.to_s).strftime("%H:%M:%S")}', '#{Date.parse(qt.date.to_s).to_s}', 
            #{qt.averageDailyVolume.to_f});"
          
          puts sql
        
          @mycon.query(sql)
          
        else
          puts "Invalid record detected for quote #{qt.to_s}"
          puts "skipping record"
        end
                
    end
      
  end
  
  def loadexchange(exchange)
    
    symres = @mycon.query("select * from symbols where exchange = \"#{exchange}\"")  
    symlist = []
    symres.each do |symbol|
      symlist << symbol[0].to_s
    end
    chunksize = 200
    nchunks = symlist.length.to_f / chunksize.to_f
    nchunks = nchunks.ceil
    (0..nchunks-1).each do |chunk|
      sidx = chunk * chunksize
      eidx = sidx + chunksize -1
      if (eidx > symlist.length - 1)
        eidx = symlist.length - 1
      end
      
      loadstandardquote(symlist[sidx..eidx].join(",").to_s)
      
    end
    
    
    
  
  end
    
      
end